﻿using log4net;
using MySql.Data.MySqlClient;
using System;

namespace per.esmool.utils.DB.MySQL {

    public abstract class MySQLModel<T> : Dao<T, MySqlConnection> where T : IIDData<T>, new() {

        private static readonly ILog LOG = LogManager.GetLogger(typeof(MySQLModel<T>));

        public MySQLModel() { }

        readonly DbFieldKit<T> kit = new();

        public abstract string TableName { get; }

        public string CreateParameterName(string parameterName) {
            return $"@{parameterName}";
        }

        public int Insert(T entity) {
            var data = kit.GetAllFields(entity);
            (var names, var parameters) = kit.BuildTokens<MySqlParameter>(data, CreateParameterName);
            var nameJoiner = new StringJoiner(", ");
            var valueJoiner = new StringJoiner(", ");
            foreach (var name in names) {
                if (name == "id")
                    continue;
                var pName = $"@{name}";
                nameJoiner.Add(name);
                valueJoiner.Add(pName);
            }

            int result = -1;
            RunTransaction<MySqlTransaction>(trans => {
                var tableName = TableName;
                var sqlInsert = $"insert into {tableName} ({nameJoiner}) values ({valueJoiner})";
                using var cmdInsert = CreateCommand<MySqlCommand, MySqlTransaction>(sqlInsert, trans);
                foreach (var parameter in parameters)
                    PutParameter(cmdInsert, parameter);

                LOG.Debug($"inserting: {cmdInsert}");
                result = cmdInsert.ExecuteNonQuery();
                LOG.Debug($"inserted: {result}");

                var sqlQuery = $"select seq from sqlite_sequence where name='{tableName}' limit 1";
                using var cmdQuery = CreateCommand<MySqlCommand, MySqlTransaction>(sqlQuery, trans);
                var r = cmdQuery.ExecuteScalar();
                result = Convert.ToInt32(r);
                entity.Id = result;
            });

            return result;
        }
    }
}
